package com.clh.dao.impl;


import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.clh.dao.CarDao;
import com.clh.entity.Car;
import com.clh.entity.Owner;
import com.clh.util.DBUtil;

public class CarDaoImpl implements CarDao{

	@Override
	public List<Car> find() {
		List<Car> list = new ArrayList<Car>();
		StringBuilder sql = new StringBuilder();
		sql.append("select")
		.append(" A.car_id,")
		.append(" A.car_username,")
		.append(" A.car_card,")
		.append(" A.car_phone,")
		.append(" A.room_no")
		.append(" from car A")
		.append(" inner join owner B")
		.append(" on A.room_no = B.room_no");
		ResultSet rs = DBUtil.query(sql.toString());
		
		try {
			while(rs.next()) {
				String carId = rs.getString("car_id");
				String carUserName = rs.getString("car_username");
				String carCard = rs.getString("car_card");
				String carPhone = rs.getString("car_phone");
				String roomNo = rs.getString("room_no");
				
				
				Car car = new Car();
				car.setCarId(carId);
				car.setCarUserName(carUserName);
				car.setCarCard(carCard);
				car.setCarPhone(carPhone);
				
				Owner owner = new Owner();
				owner.setRoomNo(roomNo);
				car.setOwner(owner);
				
				list.add(car);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public int insert(Car car) {
		StringBuilder sql = new StringBuilder();
		sql.append("insert into car(car_id,car_username,car_card,car_phone,room_no) ")
		.append(" values (?,?,?,?,?)");
		
		int n = DBUtil.update(sql.toString(),
				car.getCarId(),car.getCarUserName(),car.getCarCard(),car.getCarPhone(),car.getOwner().getRoomNo());
		return n;
	}

	@Override
	public int deleteById(String carId) {
		
		StringBuilder sql = new StringBuilder();
		sql.append("delete from car where car_id=?");
		
		int n = DBUtil.update(sql.toString(), carId);

		return n;
	}

	@Override
	public int update(Car car) {
		return DBUtil.update("update car set car_username = ?,car_card = ?,car_phone = ?,room_no = ? where car_id = ?", 
				car.getCarUserName(),car.getCarCard(),car.getCarPhone(),car.getOwner().getRoomNo(),car.getCarId());
	}

	@Override
	public List<Car> findByCindition(Car car) {
		List<Car> list = new ArrayList<Car>();
		StringBuilder sql = new StringBuilder();
		sql.append("select")
		.append(" A.car_id,")
		.append(" A.car_username,")
		.append(" A.car_card,")
		.append(" A.car_phone,")
		.append(" A.room_no")
		.append(" from car A")
		.append(" inner join owner B")
		.append(" on A.room_no = B.room_no");
		
		//
		if(true) {
			sql.append(" where 1 = 1");
		}
		
		if(car.getCarId() != null && !"".equals(car.getCarId())) {
			sql.append(" and A.car_id like '" + car.getCarId() + "'");
		}
		if(car.getCarCard() != null && !"".equals(car.getCarCard())) {
			sql.append(" and A.car_card like '" + car.getCarCard() + "'");
		}
		if(car.getCarUserName() != null && !"".equals(car.getCarUserName())) {
			sql.append(" and A.car_username like '" + car.getCarUserName() + "'");
		}
		if(car.getCarPhone() != null && !"".equals(car.getCarPhone())) {
			sql.append(" and A.car_phone like '" + car.getCarPhone() + "'");
		}
		if(car.getOwner().getRoomNo() != null && !"".equals(car.getOwner().getRoomNo())) {
			sql.append(" and A.room_no like '" + car.getOwner().getRoomNo() + "'");
		}
		
		ResultSet rs = DBUtil.query(sql.toString());
		
		try {
			while(rs.next()) {
				String carId = rs.getString("car_id");
				String carUserName = rs.getString("car_username");
				String carCard = rs.getString("car_card");
				String carPhone = rs.getString("car_phone");
				String roomNo = rs.getString("room_no");
				
				
				Car car2 = new Car();
				car2.setCarId(carId);
				car2.setCarUserName(carUserName);
				car2.setCarCard(carCard);
				car2.setCarPhone(carPhone);
				
				Owner owner = new Owner();
				owner.setRoomNo(roomNo);
				car2.setOwner(owner);
				
				list.add(car2);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public List<Car> findByPage(Car car, int pageNum, int pageSize) {
		List<Car> list = new ArrayList<Car>();
		StringBuilder sql = new StringBuilder();
		sql.append("select")
		.append(" A.car_id,")
		.append(" A.car_username,")
		.append(" A.car_card,")
		.append(" A.car_phone,")
		.append(" A.room_no")
		.append(" from car A")
		.append(" inner join owner B")
		.append(" on A.room_no = B.room_no");
		
		
		if(true) {
			sql.append(" where 1 = 1");
		}
		
		if(car.getCarId() != null && !"".equals(car.getCarId())) {
			sql.append(" and A.car_id like '" + car.getCarId() + "'");
		}
		if(car.getCarCard() != null && !"".equals(car.getCarCard())) {
			sql.append(" and A.car_card like '" + car.getCarCard() + "'");
		}
		if(car.getCarUserName() != null && !"".equals(car.getCarUserName())) {
			sql.append(" and A.car_username like '" + car.getCarUserName() + "'");
		}
		if(car.getCarPhone() != null && !"".equals(car.getCarPhone())) {
			sql.append(" and A.car_phone like '" + car.getCarPhone() + "'");
		}
		
		
		
		sql.append(" limit " + (pageNum-1)*pageSize + "," +pageSize); //limit 0,2
		
		ResultSet rs = DBUtil.query(sql.toString());
		
		try {
			while(rs.next()) {
				String carId = rs.getString("car_id");
				String carUserName = rs.getString("car_username");
				String carCard = rs.getString("car_card");
				String carPhone = rs.getString("car_phone");
				String roomNo = rs.getString("room_no");
				
				
				Car car2 = new Car();
				car2.setCarId(carId);
				car2.setCarUserName(carUserName);
				car2.setCarCard(carCard);
				car2.setCarPhone(carPhone);
				
				Owner owner = new Owner();
				owner.setRoomNo(roomNo);
				car2.setOwner(owner);
				
				list.add(car2);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public int countByCondition(Car car) {
		int n =0;
		StringBuilder sql = new StringBuilder();
		sql.append("select")
		.append(" count(*)")
		.append(" from car A ")
		.append(" inner join owner B").append(" on A.room_no = B.room_no");
		
		
		if(true){
			sql.append(" where 1 = 1");
		}
		
		
		if(car.getCarId() != null && !"".equals(car.getCarId())) {
			sql.append(" and A.car_id like '" + car.getCarId() + "'");
		}
		if(car.getCarCard() != null && !"".equals(car.getCarCard())) {
			sql.append(" and A.car_card like '" + car.getCarCard() + "'");
		}
		if(car.getCarUserName() != null && !"".equals(car.getCarUserName())) {
			sql.append(" and A.car_username like '" + car.getCarUserName() + "'");
		}
		if(car.getCarPhone() != null && !"".equals(car.getCarPhone())) {
			sql.append(" and A.car_phone like '" + car.getCarPhone() + "'");
		}
		
		ResultSet rs = DBUtil.query(sql.toString());
		
		try {
			while(rs.next()) {
				
				n = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return n;
	}

	@Override
	public Car findById(String carId) {
		Car car = null;
		List<Car> list = new ArrayList<Car>();
		StringBuilder sql = new StringBuilder();
		sql.append("select")
		.append(" A.car_id,")
		.append(" A.car_username,")
		.append(" A.car_card,")
		.append(" A.car_phone,")
		.append(" A.room_no")
		.append(" from car A")
		.append(" inner join owner B")
		.append(" on A.room_no = B.room_no")
		.append(" where A.car_id = ? ");
		ResultSet rs = DBUtil.query(sql.toString(),carId);
		
		try {
			while(rs.next()) {
				
				String carUserName = rs.getString("car_username");
				String carCard = rs.getString("car_card");
				String carPhone = rs.getString("car_phone");
				String roomNo = rs.getString("room_no");
				
				
				car = new Car();
				car.setCarId(carId);
				car.setCarUserName(carUserName);
				car.setCarCard(carCard);
				car.setCarPhone(carPhone);
				
				Owner owner = new Owner();
				owner.setRoomNo(roomNo);
				car.setOwner(owner);
				
			
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		
		return car;
	}

	@Override
	public List<Car> findByPhone(String carPhone) {
		Car car = null;
		List<Car> list = new ArrayList<Car>();
		StringBuilder sql = new StringBuilder();
		sql.append("select")
		.append(" A.car_id,")
		.append(" A.car_username,")
		.append(" A.car_card,")
		.append(" A.car_phone,")
		.append(" A.room_no")
		.append(" from car A")
		.append(" inner join owner B")
		.append(" on A.room_no = B.room_no")
		.append(" where A.car_phone = ? ");
		ResultSet rs = DBUtil.query(sql.toString(),carPhone);
		
		try {
			while(rs.next()) {
				
				String carId = rs.getString("car_id");
				String carUserName = rs.getString("car_username");
				String carCard = rs.getString("car_card");
				String roomNo = rs.getString("room_no");
				
				
				car = new Car();
				car.setCarId(carId);
				car.setCarUserName(carUserName);
				car.setCarCard(carCard);
				car.setCarPhone(carPhone);
				
				Owner owner = new Owner();
				owner.setRoomNo(roomNo);
				car.setOwner(owner);
				
				list.add(car);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		
		return list;
	}
	
}
